<?php
require __DIR__ . '/../../web/new/cron.php';

use artisan\db;

class AddIndex
{
    const DB_NAME = 'kd_address';
    /**
     * @var \artisan\db;
     */
    private static $dbInstance;

    private $allMatchedTables = [];

    private $targetTables = [];

    private $startWith = 'tbl_addresses_';

    const INDEX_NAME = 'branch_and_courier';

    const INDEX_SQL_TPL = 'ALTER TABLE `%s` ADD INDEX `branch_and_courier` (`branch_code`, `courier_code`) USING BTREE ;';

    const GET_TABLES_SQL = 'SHOW TABLES';

    private static $db;


    /**
     * main
     */
    public function main()
    {
        $this->getTargetTableNames();
        $this->filterIndexExistTable();
        $this->applyIndex();
    }

    /**
     * AddIndex
     */
    private function applyIndex()
    {
        if($this->targetTables){
            foreach ($this->targetTables as $table){
                $sql = self::generateSql(self::INDEX_SQL_TPL, [$table]);
                echo $sql,PHP_EOL;
                echo $this->query($sql) ? '执行成功' : '执行失败';
            }
        }
    }

    /**
     * @param string $tpl
     * @param array $vars
     * @return string
     */
    private static function generateSql($tpl, array $vars)
    {
        return vsprintf($tpl, $vars);
    }

    /**
     * @param null $startWith
     * @return array
     */
    private function getTargetTableNames($startWith = null)
    {
        $startWith === null and $startWith = $this->startWith;
        $sql = self::GET_TABLES_SQL.' LIKE "'.$startWith.'%"';
        if (($result = $this->query($sql)) && is_array($result)) {
            foreach ($result as $table) {
                if (isset($table[0]) && stripos($table[0], $startWith) === 0) {
                    $this->targetTables[] = $table[0];
                }
            }
        }
        return $this->allMatchedTables =  $this->targetTables;
    }

    /**
     * @param string $sql
     * @param \artisan\db $db
     * @return array|bool|int|mixed
     */
    private function query($sql = '', db $db = null)
    {
        if(!$sql){
            return false;
        }elseif ($db === null) {
            if(!self::$dbInstance){
                self::$dbInstance = db::connect(self::DB_NAME);
            }
            $db = self::$dbInstance;
        }

        if (($result = $db->query($sql)) && $result instanceof mysqli_result) {
            if(function_exists('mysqli_fetch_all')){
                $result = $resource->fetch_all($fetch_type);
            }else{
                while ($row = $resource->fetch_array($fetch_type)) {
                    $result[] = $row;
                }
            }
        }
        return $result;
    }

    /**
     * filterIndexExistTable
     * @return array
     */
    private function filterIndexExistTable()
    {
        if(!$this->targetTables){
            return [];
        }

        $tpl = "SELECT INDEX_NAME AS idx,TABLE_NAME AS tbl FROM STATISTICS WHERE TABLE_SCHEMA = '%s' AND TABLE_NAME  IN (%s) AND INDEX_NAME = '%s' GROUP BY TABLE_NAME";
        echo $sql = sprintf($tpl, self::DB_NAME, "'".(implode("','", $this->targetTables))."'", self::INDEX_NAME);
        if(self::$db === null){
            self::$db = new db();
            self::$db->config = [
                'host' => '192.168.1.81',
                'user' => 'dbworker',
                'pass' => 'dbwokerpwd',
                'db' => 'information_schema',
                'port' => '3306',
                // 从库配置
                'slaves' => [],
            ];
        }
        if(($result = $this->query($sql, self::$db)) && is_array($result)){
            $existTables = array_column($result, 'tbl');
            $this->targetTables =  array_diff($this->targetTables, $existTables);
        }
        var_dump($result,$this->targetTables);exit;
        return $this->targetTables;
    }
}

(new AddIndex())->main();
